﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_Execute SQL Parameters and Result Sets Sample</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:08/05/2008 01:28:33-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_Execute SQL Parameters and Result Sets Sample</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 08/05/2008 01:28:33</p>
        
        <font color="DarkGray"> </font><p /> 
        <span id="changeHistory">
        </span>
    <p>This sample works only with Microsoft SQL Server 2008. This sample will not work with any version of SQL Server earlier than SQL Server 2008.</p>
    <p>This sample works with the SQL Server 2005 version of the <b>AdventureWorks</b> OLTP database. To install this database, see <a href="http://go.microsoft.com/fwlink/?LinkId=122077" alt="blocked::http://go.microsoft.com/fwlink/?LinkId=122077"><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Sample Databases for Microsoft SQL Server 2008</linkText></a>.</p>
    <p>The Execute SQL Parameters and Result Sets sample is a package that uses a parameterized stored procedure and an SQL statement to query the <b>AdventureWorks</b> database. The package stores the query results in package variables by mapping parameters and result sets to the variables.</p>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Note: </th></tr><tr><td>
      This sample package demonstrates how to configure the Execute SQL task to use parameters and result sets. However, this is not the typical approach to querying related tables and returning data. A best practice for such a query is to use a Lookup transformation that connects to a source component and a destination component.<p />
    </td></tr></table><p /></div>
    <p>To query the related tables and return data, this sample package uses the following Integration Services components:</p>
    <ul><li>
        An Execute SQL task that creates a stored procedure that runs later in the package.<br />
      </li><li>
        A second Execute SQL task that queries a view in the <b>AdventureWorks</b> database for employee last names, and then stores the table rows in a variable of the <b>Object</b> data type.<br />
      </li><li>
        A Foreach Loop container that extracts the table row values, which are stored in the variable of the <b>Object</b> data type, into separate variables. To extract and separate values, the Foreach Loop container uses these Integration Services components: <br />
        <ul><li>
            An Execute SQL task that executes the stored procedure, which uses input and output parameters mapped to package variables, to query the <b>AdventureWorks</b> database for the sales total for each employee. <br />
          </li><li>
            A script task that writes the sales totals and employee last names to a file.<br />
          </li></ul>
      </li></ul>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
      Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.<p />
    </td></tr></table><p /></div>
  <h1 class="heading">Requirements</h1><div id="requirementsSection" class="section">
    <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">To run this sample package, the following components are required:</p>
      <ul xmlns=""><li>
          The sample package and data files that the package uses must be installed on the local hard disk drive. <br />
        </li><li>
          You must have installed and have administrative permissions on the <b>AdventureWorks</b> OLTP database.<br />
        </li><li>
          If you run the sample package only from the command line, you must install Integration Services. <br />
        </li><li>
          If you open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.<br />
        </li></ul>
      <p xmlns="">For more information about how to install samples, see "Considerations for Installing SQL Server Samples and Sample Databases" in SQL Server Books Online.</p>
    </content>
  </div><h1 class="heading">Location of the Sample Package</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If you installed the samples to the default installation location, the Execute SQL Parameters and Result Sets sample is located in the following folder: </p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ ExecuteSQLParametersResultSets Sample\ExecuteSQLParametersResultSets</p>
      <p xmlns="">To run this sample package, the files listed in the following table must be stored on the computer that is running the package.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              File
            </th>
            <th>
              Description
            </th>
          </tr><tr>
          <td>
            <p>ExecuteSQLParametersResultsSets.dtsx</p>
          </td>
          <td>
            <p>The sample package.</p>
          </td>
        </tr><tr>
          <td>
            <p>GetEmployeeSalesYTD.sql</p>
          </td>
          <td>
            <p>The stored procedure that returns employee last names from a view in the <b>AdventureWorks</b> database.</p>
          </td>
        </tr><tr>
          <td>
            <p>CheckQueryResults.txt</p>
          </td>
          <td>
            <p>The text file that contains the results of the query that the connected Execute SQL task runs.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Running the Sample</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The package can be run from the command line by using the <b>dtexec</b> utility (dtexec.exe), or can be run in Business Intelligence Development Studio.</p>
      <p xmlns="">If you are using a non-English version of Windows, you might have to update the <b>ConnectionString</b> property of any file connection managers that the package uses. Otherwise, the sample package might not run successfully. Before running the package, do the following:</p>
      <ul xmlns=""><li>
          Verify that the path in the <b>ConnectionString</b> property of any file connection managers is valid on your computer. This sample includes the Check Query Results and GetEmployeeSalesYTD.sql connection managers.<br />
        </li><li>
          If the path is not valid, modify the path so that the path uses the localized name of the Program Files folder..<br />
        </li></ul>
    </content><sections xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <h3 class="subHeading" xmlns="">Running the Sample at the Command Prompt</h3><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">Use the following procedure to use the <b>dtexec</b> utility to run this sample package. For more information about how use the <b>dtexec</b> utility to runs a package, see the topic, "dtexec Utility," in SQL Server Books Online.</p>
          <h4 class="procedureHeading" xmlns="">To run the package by using dtexec</h4><div id="procedureSectionEBBDBFBHA" class="section" xmlns=""><ol><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">Open a Command Prompt window.</p>
                </content>
              </li><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">At the command prompt, change the directory to <i>&lt;drive&gt;</i>:\Program Files\Microsoft SQL Server\100\DTS\Binn.</p>
                  <p xmlns="">This directory contains the <b>dtexec</b> utility.</p>
                </content>
              </li><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">Type the following command:</p>
                  <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ ExecuteSQLParametersResultSets Sample\ExecuteSQLParametersResultSets \ExecuteSQLParametersResultSets.dtsx "</pre></td></tr></table></span></div>
                </content>
              </li><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">Press ENTER.</p>
                </content>
              </li></ol></div>
        </content></div>
      <h3 class="subHeading" xmlns="">Running the Sample at the Command Prompt</h3><div class="subSection" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">Use the following procedure to use Business Intelligence Development Studio to run this sample package.</p>
          <h4 class="procedureHeading" xmlns="">To run the package in Business Intelligence Development Studio</h4><div id="procedureSectionEBBBBFBHA" class="section" xmlns=""><ol><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">Open Business Intelligence Development Studio.</p>
                </content>
              </li><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">Create a new project and a new Integration Services solution.</p>
                  <p xmlns="">
                    Business Intelligence Development Studio adds the new solution to <b>Solution Explorer</b> and the new project to the solution. For more information, see <b>How to: Create a New Integration Services Project</b>.</p>
                </content>
              </li><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">In <b>Solution Explorer</b>, right-click the <b>SSIS Packages</b> folder, and then click <b>Add Existing Package</b>.</p>
                </content>
              </li><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">Add the ExecuteSQLParametersResultSets.dtsx package by doing the following steps:</p>
                  <ol xmlns=""><li>
                      In the <b>Package location</b> list, select <b>File System</b>.<br />
                    </li><li>
                      Click the browse button <b>(...)</b>, select the ExecuteSQLParametersResultSets.dtsx package, and then click <b>OK</b>.<br />
                    </li></ol>
                </content>
              </li><li>
                <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
                  <p xmlns="">In <b>Solution Explorer</b>, in the <b>SSIS Packages</b> folder, right-click ExecuteSQLParametersResultSets.dtsx, and then click <b>Execute Package</b>.</p>
                </content>
              </li></ol></div>
        </content></div>
    </sections></div><h1 class="heading">Components in the Sample</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The following table lists the Integration Services tasks and connection managers that the sample uses.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              Element
            </th>
            <th>
              Purpose
            </th>
          </tr><tr>
          <td>
            <p>Execute SQL task</p>
          </td>
          <td>
            <p>The Execute SQL task, <b>Create Stored Procedure</b>, creates a stored procedure that the Execute SQL task, <b>Query for Year-to-Date Sales Totals</b>, runs.</p>
          </td>
        </tr><tr>
          <td>
            <p>Execute SQL task</p>
          </td>
          <td>
            <p>The Execute SQL task, <b>Query for Employee Last Names</b>, runs an SQL statement that queries the <b>vEmployee</b> view in the <b>AdventureWorks</b> database. The task stores the results as a <b>Full result set</b> in a package variable of <b>Object</b> data type, <b>SQLQueryResults_Name</b>. </p>
            <p>The SQL statement returns records with the <b>LastName</b> column value that is based on the value of the <b>EmployeeID</b> column. </p>
          </td>
        </tr><tr>
          <td>
            <p>Foreach Loop container</p>
          </td>
          <td>
            <p>The Foreach Loop container, <b>Enumerate Rows Containing Last Name</b>, iterates through each table row that is stored in the <b>SQLQueryResults_Name</b> variable and extracts the column value into a package variable. The variable is mapped to the column. The Foreach Loop container uses the <b>Foreach ADO Enumerator</b> to enumerate the table rows.</p>
          </td>
        </tr><tr>
          <td>
            <p>Execute SQL task</p>
          </td>
          <td>
            <p>The Execute SQL task, <b>Query for Year-to-Date Sales Totals</b>, uses an SQL statement to run the stored procedure that returns employee last names from a view in the <b>AdventureWorks</b> database.</p>
            <p>The SQL statement includes parameters mapped to package variables. The input parameter passes the <b>LastName</b> variable value to the stored procedure. The output parameter stores the sales value, which the stored procedure returns, in the <b>SalesYTD</b> variable.</p>
            <p>This Execute SQL task is in the Foreach Loop container, <b>Enumerate Rows Containing Last Name</b>.</p>
          </td>
        </tr><tr>
          <td>
            <p>Script task</p>
          </td>
          <td>
            <p>The Script task, <b>Write to File Employee Names and Year-to-Date Sales Totals</b>, writes the sales totals and employee last names that are stored in package variables to a file. </p>
            <p>This Script task is in the Foreach Loop container, <b>Enumerate Rows Containing Last Name</b>.</p>
          </td>
        </tr><tr>
          <td>
            <p>Flat File connection manager</p>
          </td>
          <td>
            <p>The Flat File connection manager, <b>Check Query Results</b>, connections to the file to which the Script task writes.</p>
          </td>
        </tr><tr>
          <td>
            <p>File connection manager</p>
          </td>
          <td>
            <p>The File connection manager, <b>GetEmployeeSalesYTD.sql</b>, connects to the file that contains the stored procedure.</p>
          </td>
        </tr></table>
    </content></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>